package com.power.sifa.info.dao.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.plf.core.common.dao.OffersetTransformers;
import com.power.sifa.info.model.Law_doc;
import com.power.sifa.info.model.Law_provision;
import com.power.sifa.info.model.pageInfo.Law_docKeywordPageInfo;
import com.power.sifa.info.model.pageInfo.Law_docPageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.query.internal.NativeQueryImpl;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.power.sifa.info.dao.Law_docDaoCustom;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
* 法律表 持久层实现(hibernate)
*/

@Slf4j
@Repository
public class Law_docDaoImpl implements Law_docDaoCustom{

    @Resource
    private EntityManager entityManager;

    @Resource
    private JdbcTemplate jdbcTemplate;


    /**
     * 重写的分页查询方法
     * @param pageInfo 查询条件
     * @return
     */
    @Override
    public List pageQuery(Law_docPageInfo pageInfo) {

        List<Law_doc> result = new ArrayList<>();

        StringBuffer sql = new StringBuffer("select a.*\n" +
                "from (\n" +
                "   select t.*,\n" +
                "\t\t  row_number() over (\n" +
                "\t\t\t  partition by groups order by versions desc\n" +
                "\t\t  ) as rn\n" +
                "\tfrom law_doc t\n" +
                ") as a\n" +
                " where rn = 1");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;
        if (StringUtils.isNotEmpty(pageInfo.getF_title())) {
            sql.append(" and A.title like ?" + index++);
            paramsList.add("%" + pageInfo.getF_title() + "%");
        }
        if (ObjectUtil.isNotNull(pageInfo.getF_type())) {
            sql.append(" and A.type = ?" + index++);
            paramsList.add(pageInfo.getF_type());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_pubunitid())) {
            sql.append(" and A.pubunitid = ?" + index++);
            paramsList.add(pageInfo.getF_pubunitid());
        }
        /**
         * 发布日期
         */
        if (StringUtils.isNotEmpty(pageInfo.getF_pubStartDate())) {
            sql.append(" and A.pubdate >= ?" + index++);
            paramsList.add(pageInfo.getF_pubStartDate());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_pubEndDate())) {
            sql.append(" and A.pubdate <= ?" + index++);
            paramsList.add(pageInfo.getF_pubEndDate());
        }
        /**
         * 施行日期
         */
        if (StringUtils.isNotEmpty(pageInfo.getF_execStartDate())) {
            sql.append(" and A.execdate >= ?" + index++);
            paramsList.add(pageInfo.getF_execStartDate());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_execEndDate())) {
            sql.append(" and A.execdate <= ?" + index++);
            paramsList.add(pageInfo.getF_execEndDate());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_status())) {
            sql.append(" and A.status = ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_source())) {
            sql.append(" and A.source = ?" + index++);
            paramsList.add(pageInfo.getF_source());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_pubcode())) {
            sql.append(" and A.pubcode like ?" + index++);
            paramsList.add("%" + pageInfo.getF_pubcode() + "%");
        }
        /**
         * 失效日期
         */
        if (StringUtils.isNotEmpty(pageInfo.getF_loseStartDate())) {
            sql.append(" and A.losedate >= ?" + index++);
            paramsList.add(pageInfo.getF_loseStartDate());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_loseEndDate())) {
            sql.append(" and A.losedate <= ?" + index++);
            paramsList.add(pageInfo.getF_loseEndDate());
        }

        /**
         * 收录日期
         */
        if (StringUtils.isNotEmpty(pageInfo.getF_recordStartDate())) {
            sql.append(" and A.recorddate >= ?" + index++);
            paramsList.add(pageInfo.getF_recordStartDate());
        }
        if (StringUtils.isNotEmpty(pageInfo.getF_recordEndDate())) {
            sql.append(" and A.recorddate <= ?" + index++);
            paramsList.add(pageInfo.getF_recordEndDate());
        }
        
        // 排序
        //sql.append(" order by A.groups desc " + pageInfo.getSort() + " " + pageInfo.getOrder());
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                // 添加对应的格式转换
                result = JSON.parseArray(JSON.toJSONString(list), Law_doc.class);
            }
        }
        return result;
    }


    /**
     *  拼装标题关键字查询的sql
     * @param keyWords
     * @return
     */
    private StringBuffer generateDocSearchSql(List<String> keyWords) {
        StringBuffer stringBuffer = new StringBuffer("select * from sfapprove.law_doc s where 1=1 ");
        if (CollectionUtil.isNotEmpty(keyWords)) {
            for (int index = 0; index < keyWords.size(); index++) {
                String keyWord = keyWords.get(index);
                if (index == 0) {
                    stringBuffer.append(" and ( ");
                    stringBuffer.append(" s.title like " + "\'%" + keyWord + "%\'");
                } else {
                    stringBuffer.append(" or s.title like " + "\'%" + keyWord + "%\'" );
                }
            }
            stringBuffer.append(" ) ");
        }
        return stringBuffer;
    }

    /**
     * 拼装正文查询的sql
     * @param
     * @return
     */
    private StringBuffer generateDocDetailSearchSql() {
        StringBuffer sqlBuff = new StringBuffer("select * from law_doc t WHERE EXISTS (\n" +
            "\t\tselect 1 from (\n" +
            "\t\t\tselect DISTINCT s.docid from (\n" +
            "\t\t\t\tselect Distinct a.docid, ts_rank(a.convector, tsq) score\n" +
            "\t\t\t\t\t\tfrom sfapprove.law_provision a, to_tsquery(?) tsq\n" +
            "\t\t\t\t\t\twhere a.convector@@tsq\n" +
            "\t\t\t\t\t\tgroup by docid, score order by score desc\n" +
            "\t\t\t\t) s \n" +
            "\t\t ) p  where t.id = p.docid\n" +
            ") ");
        return sqlBuff;
    }

    /**
     * 拼接查询条件
     * @param pageInfo 查询条件
     * @return
     */
    private StringBuffer generateSearchSql(Law_docKeywordPageInfo pageInfo, Boolean isCount) {
        StringBuffer sqlBuff = new StringBuffer();
        SimpleDateFormat timeFomrat = new SimpleDateFormat("yyyy-MM-dd");
        if (ObjectUtil.isNotEmpty(pageInfo.getPubStartDate())) {
            sqlBuff.append( " and pubdate >= ").append(timeFomrat.format(pageInfo.getPubStartDate()));
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getPubEndDate())) {
            sqlBuff.append( " and pubdate <= ").append(timeFomrat.format(pageInfo.getPubEndDate()));
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getExecStartDate())) {
            sqlBuff.append( " and execdate <= ").append(timeFomrat.format(pageInfo.getExecStartDate()));
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getExecEndDate())) {
            sqlBuff.append( " and execdate >= ").append(timeFomrat.format(pageInfo.getExecEndDate()));
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getLawType())) {
            sqlBuff.append( " and type = ").append(pageInfo.getLawType());
        }
        if (!isCount) {
            if (ObjectUtil.isNotEmpty(pageInfo.getSort())) {
                sqlBuff.append( " order by ").append(pageInfo.getSort());
                if (ObjectUtil.isNotEmpty(pageInfo.getOrder())) {
                    sqlBuff.append(" ").append(pageInfo.getOrder()).append(" ");
                }
            }
            sqlBuff.append(" limit ") .append(pageInfo.getPageNumber() - 1 >=0 ? pageInfo.getPageNumber() - 1: 0).append(",").append(pageInfo.getPageSize());
        }
        return sqlBuff;
    }

    /**
     * 法律详情- 关键字查询法律法规
     * @param pageInfo 查询条件
     * @return
     */
    @Override
    public List<Law_doc> searchDocByKeyWord(Law_docKeywordPageInfo pageInfo) {
        List<String> conWord = pageInfo.getConWord();
        StringBuffer sqlBuffer = generateDocSearchSql(conWord);
        StringBuffer countStringBuff = new StringBuffer(sqlBuffer);
        sqlBuffer.append(generateSearchSql(pageInfo,false));
        countStringBuff.append(generateSearchSql(pageInfo,true));

        String countSql = countStringBuff.toString().replaceFirst("\\*","count(*)");
        Integer countAll = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        pageInfo.setAllcount(countAll);
        List<Map<String,Object>> queryList = this.jdbcTemplate.queryForList(sqlBuffer.toString());
        if (CollectionUtil.isNotEmpty(queryList)) {
            return JSON.parseArray(JSON.toJSONString(queryList), Law_doc.class);
        }
        return Collections.emptyList();
    }


    /**
     * 法律详情- 关键字查询法律法规
     * @param pageInfo 查询条件
     * @return
     */
    @Override
    public List<Law_doc> searchDocDetailByKeyWord(Law_docKeywordPageInfo pageInfo) {
        // 查询关键字查询的法律法规
        StringBuffer sqlBuffer = generateDocDetailSearchSql();
        StringBuffer countStringBuff = new StringBuffer(sqlBuffer);
        sqlBuffer.append(generateSearchSql(pageInfo,false));
        countStringBuff.append(generateSearchSql(pageInfo,true));

        List<String> conWords = pageInfo.getConWord();
        if (CollectionUtil.isNotEmpty(conWords)) {
            StringBuffer keywordBuff = new StringBuffer();
            for (String conWord : conWords) {
                keywordBuff.append(conWord).append("|");
            }
            String keyWordStr = keywordBuff.toString();
            if (keyWordStr.endsWith("|")) {
                int startIndex = 0;
                int endIndex = keyWordStr.length() - "|".length();
                keyWordStr = keyWordStr.substring(startIndex, endIndex);
            }
            String countSql = countStringBuff.toString().replaceFirst("\\*","count(*)");
            Integer countAll = this.jdbcTemplate.queryForObject(countSql, Integer.class, new Object[]{keyWordStr});
            pageInfo.setAllcount(countAll);

            List<Map<String,Object>> queryList = this.jdbcTemplate.queryForList(sqlBuffer.toString(),new Object[]{keyWordStr});
            if (CollectionUtil.isNotEmpty(queryList)) {
                return JSON.parseArray(JSON.toJSONString(queryList), Law_doc.class);
            }
        } else {
            return searchDocByKeyWord(pageInfo);
        }
        return Collections.emptyList();
    }

    /**
     *  法律详情- 关键字查询法律法规全部查询
     * @param pageInfo 关键字
     * @return
     */
    @Override
    public List searchAllDocByKeyWord(Law_docKeywordPageInfo pageInfo) {
        List<String> conWords = pageInfo.getConWord();
        if (CollectionUtil.isNotEmpty(conWords)) {
            StringBuffer sqlBuffer = new StringBuffer(" select * from ( ");
            sqlBuffer.append(generateDocDetailSearchSql());
            sqlBuffer.append(" union ").append(generateDocSearchSql(conWords));
            sqlBuffer.append(" ) where 1=1 ");
            sqlBuffer.append(generateSearchSql(pageInfo, false));

            StringBuffer countStringBuff = new StringBuffer(" select count(*) from ( ");
            countStringBuff.append(generateDocDetailSearchSql());
            countStringBuff.append(" union ").append(generateDocSearchSql(conWords));
            countStringBuff.append(" ) where 1=1 ");
            countStringBuff.append(generateSearchSql(pageInfo, true));

            StringBuffer keywordBuff = new StringBuffer();
            for (String conWord : conWords) {
                keywordBuff.append(conWord).append("|");
            }
            String keyWordStr = keywordBuff.toString();
            if (keyWordStr.endsWith("|")) {
                int startIndex = 0;
                int endIndex = keyWordStr.length() - "|".length();
                keyWordStr = keyWordStr.substring(startIndex, endIndex);
            }
            Integer countAll = this.jdbcTemplate.queryForObject(countStringBuff.toString(), Integer.class,
                    new Object[]{keyWordStr});
            pageInfo.setAllcount(countAll);
            List<Map<String,Object>> queryList = this.jdbcTemplate.queryForList(sqlBuffer.toString(),new Object[]{keyWordStr});
            if (CollectionUtil.isNotEmpty(queryList)) {
                return JSON.parseArray(JSON.toJSONString(queryList), Law_doc.class);
            }
        } else {
            return searchDocByKeyWord(pageInfo);
        }
        return Collections.emptyList();
    }

    @Override
    public List historyVersion(Law_docPageInfo law_docPageInfo) {
        List<Law_doc> result = new ArrayList<>();
        String sql = "select a.* from (select t.*, row_number() over (partition by groups order by versions desc) as rn from law_doc t) as a where rn != 1 and groups = $1";

        sql = sql.replace("$1", "'"+law_docPageInfo.getF_groups()+"'");
        log.info(sql);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        if (CollectionUtil.isNotEmpty(list)) {
            result = JSON.parseArray(JSON.toJSONString(list), Law_doc.class);
        }
        return result;
    }
}